﻿Imports Microsoft.VisualBasic.Strings
Public Class frmquery
    Dim Strsql As String
    Dim _mysql As New ClsSQLhelper
#Region "Function"
    Sub Load_Table()
        Try
            Strsql = "show tables"
            Dim DT_TableList As New DataTable
            DT_TableList = _mysql.GetMYSQLDataTable(Strsql, "tablelist")
            With Me.RepositoryItemComboBox1
                For i = 0 To DT_TableList.Rows.Count - 1
                    .Items.Add(DT_TableList.Rows(i).Item(0))
                Next

            End With
        Catch ex As Exception

        End Try
    End Sub
    Sub Run_Query()
        Try
            With GridControl1
                .DataSource = Nothing
                GridView1.Columns.Clear()

                Strsql = Me.MemoSql.Text


                Dim DT_Sqltemp As New DataTable
                Dim SQLResult As ClsSQLResult

                SQLResult = _mysql.Get_MYSQLDataTable(Strsql, "TempTable")


                .DataSource = SQLResult.QryDataTables


                .Refresh()


                Select Case SQLResult.Result_ID
                    Case Is < 0
                        Me.TabResults.SelectedTabPage = TabpageMessage
                        Me.MemoSQLEdit.Text = "(0 row(s) affected)"
                        Me.MemoSQLEdit.Text = Me.MemoSQLEdit.Text & vbCrLf & SQLResult.Error_ID & " : " & SQLResult.Error_String
                    Case Is >= 0
                        Me.TabResults.SelectedTabPage = TabpageGrid
                        If DT_Sqltemp.Rows.Count >= 0 Then
                            Me.MemoSQLEdit.Text = "(" & SQLResult.Result_ID & " row(s) affected)"

                        End If
                End Select

                GridView1.BestFitColumns()


            End With

            With PopupMenu1


                Strsql = "SHOW COLUMNS FROM  " & CboTablelist.EditValue
                Dim DT_ColumnList As New DataTable
                DT_ColumnList = _mysql.GetMYSQLDataTable(Strsql, "COLUMNSList")
                With ColumnListItems
                    .Strings.Clear()
                    For i = 0 To DT_ColumnList.Rows.Count - 1
                        .Strings.Add(DT_ColumnList.Rows(i).Item(0))
                    Next

                End With

            End With
        Catch ex As Exception

            MsgBox(ex.Message)
        End Try
    End Sub
#End Region
    Private Sub BtnRun_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BtnRun.ItemClick
        If MemoSql.Text = "" Then Exit Sub

        Run_Query()
    End Sub

    Private Sub frmquery_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

    End Sub

    Private Sub frmquery_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MemoSql.SetHighlighting("MySQL")
        Load_Table()

    End Sub



    Private Sub BtnExcute_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BtnExcute.ItemClick
        Try
            If MemoSql.Text = "" Then Exit Sub

            If (Microsoft.VisualBasic.Strings.Left(MemoSql.Text, 6).ToUpper) = "SELECT" Then

                Run_Query()
                Exit Sub
            End If


            Dim X As ClsSQLResult
            X = _mysql.MySQLQueryExecute(Me.MemoSql.Text)

            If X.Result_ID >= 0 Then
                Me.TabResults.SelectedTabPage = TabpageMessage

                Me.MemoSQLEdit.Text = "(" & X.Result_ID & " row(s) affected)"
            Else
                Me.MemoSQLEdit.Text = "(0 row(s) affected)"
                Me.MemoSQLEdit.Text = Me.MemoSQLEdit.Text & vbCrLf & X.Error_ID & " : " & X.Error_String
            End If
        Catch ex As Exception

            Me.MemoSQLEdit.Text = "(0 row(s) affected)"
            Me.MemoSQLEdit.Text = Me.MemoSQLEdit.Text & vbCrLf & ex.Message


        End Try

    End Sub

    Private Sub BtnClose_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BtnClose.ItemClick


        Me.Close()
    End Sub

    Private Sub CboTablelist_EditValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CboTablelist.EditValueChanged
        Me.MemoSql.Text = "Select * from " & CboTablelist.EditValue & " limit 100"
    End Sub

    Private Sub CboTablelist_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles CboTablelist.ItemClick

    End Sub

    Private Sub BtnStructure_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BtnStructure.ItemClick
        If MemoSql.Text = "" Then Exit Sub
        Me.MemoSql.Text = "DESCRIBE " & CboTablelist.EditValue
        Run_Query()
    End Sub

    Private Sub BarListItem1_ListItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ListItemClickEventArgs) Handles BarListItem1.ListItemClick
        Me.MemoSql.Text = BarListItem1.Strings.Item(BarListItem1.ItemIndex)


        ' Run_Query()
    End Sub


    Private Sub BtnExportToExcel_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs)

    End Sub

    Private Sub BarListItem2_ListItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ListItemClickEventArgs) Handles BarListItem2.ListItemClick
        If MemoSql.Text = "" Then Exit Sub
        With SaveFileDialog1
            .Title = "Save As"
            .FileName = "ExportData"



            '            .Filter = "Txt files (*.txt)|*.txt|PDF files (*.pdf)|*.Pdf|Rtf files (*.rtf)|*.rtf|Excels 2003 files (*.xls)|Excel 2007  files (*.xlsx)|*.xlsx|*.txt|All files (*.*)|*.*"
        End With
        With GridView1
            Select Case BarListItem2.ItemIndex
                Case 0
                    SaveFileDialog1.Filter = "HTML Files (*.html)|*.html"
                    If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                        .ExportToHtml(SaveFileDialog1.FileName)
                    End If

                Case 1
                    SaveFileDialog1.Filter = "PDF Files (*.PDF)|*.PDF"
                    If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                        .ExportToPdf(SaveFileDialog1.FileName)
                    End If

                Case 2

                    SaveFileDialog1.Filter = "RTF Files (*.rtf)|*.rtf"
                    If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                        .ExportToRtf(SaveFileDialog1.FileName)
                    End If
                Case 3
                    SaveFileDialog1.Filter = "Text Files (*.txt)|*.txt"
                    If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                        .ExportToText(SaveFileDialog1.FileName)
                    End If

                Case 4
                    SaveFileDialog1.Filter = "Excel 2003 Files (*.xls)|*.xls"
                    If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                        .ExportToXls(SaveFileDialog1.FileName)
                    End If

                Case 5
                    SaveFileDialog1.Filter = "Excel 2007 Files (*.xlsx)|*.xlsx"
                    If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                        .ExportToXlsx(SaveFileDialog1.FileName)
                    End If


            End Select

        End With
    End Sub

    Private Sub BtnShowtables_ItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BtnShowtables.ItemClick
        Me.MemoSql.Text = "Show tables"
        Run_Query()
    End Sub

    Private Sub MemoSql_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles MemoSql.DragDrop
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            Dim MyFiles() As String
            ' Assign the files to an array.
            MyFiles = e.Data.GetData(DataFormats.FileDrop)
            ' Display the file Name
            'TextBoxDrop.Text = MyFiles(0)
            ' Display the file contents
            MemoSql.Text = My.Computer.FileSystem.ReadAllText(MyFiles(0))
        End If

    End Sub

    Private Sub MemoSql_DragEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles MemoSql.DragEnter
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            e.Effect = DragDropEffects.All
        End If

    End Sub

    Private Sub BarListItem3_ListItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ListItemClickEventArgs) Handles BarListItem3.ListItemClick

    End Sub

    Private Sub ColumnListItems_ListItemClick(ByVal sender As System.Object, ByVal e As DevExpress.XtraBars.ListItemClickEventArgs) Handles ColumnListItems.ListItemClick
        Clipboard.SetText(ColumnListItems.Strings.Item(ColumnListItems.ItemIndex))
        Dim iData As IDataObject = Clipboard.GetDataObject()
        If iData.GetDataPresent(DataFormats.Text) Then
            'If it's text, then paste it into the textbox
            MemoSql1.SelectedText = CType(iData.GetData(DataFormats.Text), String)
        Else
            'If it's not text, print a warning message
            MsgBox("Data in the clipboard is not availble")
        End If



    End Sub
End Class